﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChBusiness;
using ChBusiness.Struct;

namespace ExtBusiness.Common
{
    /// <!--
    /// 功能描述  : 文件上传操作类
    /// 创建人  : LinJian
    /// 创建时间: 2007-07-09
    /// -->
    public class UploadFileClassNew
    {
        Database dbGloab = BasicOperate.CreateNetOfficeDataBase();
        private string _newId;

        /// <summary>
        /// 新生成的id
        /// </summary>
        public string NewId
        {
            get
            {
                return _newId;
            }
            set
            {
                _newId = value;
            }
        }

        /// <summary>
        /// 验证上传文件后缀是否有效
        /// </summary>
        /// <param name="strFileExtension">文件扩展名</param>
        /// <returns></returns>
        public bool HaveValideFileExtension(string strFileExtension)
        {
            bool bOk = false;

            for (int i = 0; i < SystemConfig.UploadFileExtension.Length; i++)
            {
                if (strFileExtension.CompareTo(SystemConfig.UploadFileExtension[i]) == 0)
                {
                    bOk = true;
                    break;
                }
            }

            return bOk;
        }

        /// <summary>
        /// 获取存储文件信息
        /// </summary>
        /// <param name="fileUpload">文件上传信息</param>
        /// <returns>返回执行结果</returns>
        public object GetStoreFileType(FileUploadStruct fileUpload)
        {
            string strSql = string.Format(@"select {0} from {1} where {2}=:keyid",
                fileUpload.FileTypeFieldName,fileUpload.TableName, fileUpload.KeyId);
            DbCommand cmd = dbGloab.GetSqlStringCommand(strSql);

            dbGloab.AddInParameter(cmd, "keyid", DbType.String, fileUpload.KeyIdValue);
            return dbGloab.ExecuteScalar(cmd);
        }

        /// <summary>
        /// 存储文件上传信息
        /// </summary>
        /// <param name="fileUpload">文件上传信息</param>
        /// <param name="dType">数据存储类型</param>
        /// <returns>返回执行结果</returns>
        public int StoreUploadFile(FileUploadStruct fileUpload,DataStoreType dType)
        {
            string strSql = string.Empty;
            //string strFields = string.Empty;
            //string strValues = string.Empty;
            
            if (dType == DataStoreType.Update)
            {
                strSql = string.Format(@"update {0} set {1}=:fileValue,
                {2}=:fileType,{3}=:resourceField where {4}=:keyid returning id into :newId", fileUpload.TableName, fileUpload.FileStoreFieldName, fileUpload.FileTypeFieldName, fileUpload.StoreResourceFieldName, fileUpload.KeyId);
            }
            else if (dType == DataStoreType.Insert)
            {
                if (fileUpload.TableName == "XT_SERIAL_MODULE" || fileUpload.TableName == "SYS_RESOURCE")
                {
                    strSql = string.Format(@"insert into {0}({1},{2},{3},{4},{5},flowid) values(:fileValue,
                :fileType,:keyField,:resourceField,:paramvalue,:flowid) returning id into :newId", fileUpload.TableName, fileUpload.FileStoreFieldName, fileUpload.FileTypeFieldName, fileUpload.StoreKeyFieldName, fileUpload.StoreResourceFieldName, fileUpload.StoreParamFieldName);
                }
                else
                {
                    strSql = string.Format(@"insert into {0}({1},{2},{3},{4},{5}) values(:fileValue,
                :fileType,:keyField,:resourceField,:paramvalue) returning id into :newId", fileUpload.TableName, fileUpload.FileStoreFieldName, fileUpload.FileTypeFieldName, fileUpload.StoreKeyFieldName, fileUpload.StoreResourceFieldName, fileUpload.StoreParamFieldName);
                }
            }
            DbCommand cmd = dbGloab.GetSqlStringCommand(strSql);

            dbGloab.AddInParameter(cmd, "fileValue", DbType.Binary, fileUpload.FileBytes);
            dbGloab.AddInParameter(cmd, "fileType", DbType.String, fileUpload.FileTypeFieldValue);

            if (dType == DataStoreType.Update)
            {
                dbGloab.AddInParameter(cmd, "keyid", DbType.String, fileUpload.KeyIdValue);
                dbGloab.AddInParameter(cmd, "resourceField", DbType.String, fileUpload.StoreResourceNameValue);
            }

            if (dType == DataStoreType.Insert)
            {
                dbGloab.AddInParameter(cmd, "keyField", DbType.String, fileUpload.StoreKeyValue);
                dbGloab.AddInParameter(cmd, "paramvalue", DbType.String, fileUpload.StoreParamValue);
                dbGloab.AddInParameter(cmd, "resourceField", DbType.String, fileUpload.StoreResourceNameValue);

                if (fileUpload.TableName == "XT_SERIAL_MODULE" || fileUpload.TableName == "SYS_RESOURCE")
                {
                    dbGloab.AddInParameter(cmd, "flowid", DbType.String, fileUpload.FlowIDValue);
                }
            }

            dbGloab.AddOutParameter(cmd, "newId", DbType.String, 32);

            int nReturn = dbGloab.ExecuteNonQuery(cmd);
            _newId = dbGloab.GetParameterValue(cmd, "newId").ToString();

            return nReturn;
        }

        /// <summary>
        /// 设置用途
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strKeyId">id值</param>
        /// <param name="strUseType">用途</param>
        /// <returns></returns>
        public int SetUseType(string strTableName,string strKeyId,string strUseType)
        {
            string strSql = string.Format(@"update {0} set USE_TYPE={1} where ID='{2}'",
                strTableName, strUseType, strKeyId);
            return dbGloab.ExecuteNonQuery(CommandType.Text, strSql);
        }

        /// <summary>
        /// 删除存储文件信息
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strKeyId">键值名</param>
        /// <param name="strKeyIdValue">键值</param>
        /// <returns></returns>
        public int DeleteStoreFile(string strTableName,string strKeyId,string strKeyIdValue)
        {
            string strSql = string.Format(@"delete from {0} where {1}=:keyid",
                strTableName, strKeyId);
            DbCommand cmd = dbGloab.GetSqlStringCommand(strSql);

            dbGloab.AddInParameter(cmd, "keyid", DbType.String, strKeyIdValue);
            return dbGloab.ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 获取上传的资源信息
        /// </summary>
        /// <param name="strResourceName">查找的资源名</param>
        /// <param name="strUseType">用途类型</param>
        /// <returns></returns>
        public DataTable GetResourceInfo(string strResourceName,string strUseType)
        {
            string strSql = @"select rownum 序号,id,RESOURCE_NAME,RESOURCE_TYPE,USE_TYPE,
                wynewpackage.GetItemTypeName(USE_TYPE,t.paramid) rstype,Table_name from SYS_RESOURCE t where 1=1";

            if (!string.IsNullOrEmpty(strResourceName))
            {
                strSql += string.Format(" and RESOURCE_NAME like '%{0}%'", strResourceName);
            }
            if (!string.IsNullOrEmpty(strUseType))
            {
                strSql += string.Format(" and USE_TYPE='{0}'",strUseType);
            }
            strSql += " order by orderdield";

            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取存储文件信息
        /// </summary>
        /// <param name="strTabName"></param>
        /// <param name="strKeyId"></param>
        /// <returns></returns>
        public DataTable GetStoreFile(string strTabName,string strKeyId)
        {
            string strSql = string.Format(@"select RESOURCE_NAME,RESOURCE_TYPE,RESOURCE_CONTENT from {0} where id=:keyid",
                strTabName);
            DbCommand cmd = dbGloab.GetSqlStringCommand(strSql);

            dbGloab.AddInParameter(cmd, "keyid", DbType.String,strKeyId);
            return dbGloab.ExecuteDataSet(cmd).Tables[0];
        }

       
        /// <summary>
        /// 存储接口文件上传信息
        /// </summary>
        /// <param name="fileUpload">文件上传信息</param>
        /// <param name="dType">数据存储类型</param>
        /// <returns>返回执行结果</returns>
        public int StoreAttachmentUploadFile(FileUploadStruct fileUpload, DataStoreType dType)
        {
            string strSql = string.Empty;

            if (dType == DataStoreType.Update)
            {
                strSql = string.Format(@"update {0} set {1}=:fileValue,
                {2}=:fileType where flag=0 and {3}=:keyid returning id into :newId", fileUpload.TableName,
                     fileUpload.FileStoreFieldName, fileUpload.FileTypeFieldName, fileUpload.KeyId);
            }
            if (dType == DataStoreType.Insert)
            {
                strSql = string.Format(@"insert into {0}({1},{2},{3},{4},flag) values(:fileValue,
                :fileType,:keyField,:resourceField,0) returning id into :newId", fileUpload.TableName,
                fileUpload.FileStoreFieldName, fileUpload.FileTypeFieldName, fileUpload.StoreKeyFieldName,
                fileUpload.StoreResourceFieldName);
            }
            DbCommand cmd = dbGloab.GetSqlStringCommand(strSql);

            dbGloab.AddInParameter(cmd, "fileValue", DbType.Binary, fileUpload.FileBytes);
            dbGloab.AddInParameter(cmd, "fileType", DbType.String, fileUpload.FileTypeFieldValue);

            if (dType == DataStoreType.Update)
            {
                dbGloab.AddInParameter(cmd, "keyid", DbType.String, fileUpload.KeyIdValue);
            }

            if (dType == DataStoreType.Insert)
            {
                dbGloab.AddInParameter(cmd, "keyField", DbType.String, fileUpload.StoreKeyValue);
                dbGloab.AddInParameter(cmd, "resourceField", DbType.String, fileUpload.StoreResourceNameValue);
            }

            dbGloab.AddOutParameter(cmd, "newId", DbType.String, 32);

            int nReturn = dbGloab.ExecuteNonQuery(cmd);
            _newId = dbGloab.GetParameterValue(cmd, "newId").ToString();

            return nReturn;
        }

        /// <summary>
        /// 获取接口文件上传的资源信息
        /// </summary>
        /// <param name="strWhere">查询条件</param>
        /// <returns></returns>
        public DataTable GetAttachmentResourceInfo(string strWhere)
        {
            string strSql = @"select t.*,(select a.modulename from xt_serial_module a where a.id = t.module_id) fjlb from XT_SERIAL_ATTACHMENT t where 1=1 ";

            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql += string.Format(" and {0}", strWhere);
            }

            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取接口文件存储文件信息
        /// </summary>
        /// <param name="strTabName"></param>
        /// <param name="strKeyId"></param>
        /// <returns></returns>
        public DataTable GetAttachmentStoreFile(string strTabName, string strKeyId)
        {
            string strSql = string.Format(@"select AppDataId,FILEDATA,FILETYPE,FILENAME from {0} where id=:keyid and flag=0",
                strTabName);
            DbCommand cmd = dbGloab.GetSqlStringCommand(strSql);

            dbGloab.AddInParameter(cmd, "keyid", DbType.String, strKeyId);
            return dbGloab.ExecuteDataSet(cmd).Tables[0];
        }

        /// <summary>
        /// 获取模板文件存储文件信息
        /// </summary>
        /// <param name="strTabName"></param>
        /// <param name="strKeyId"></param>
        /// <returns></returns>
        public DataTable GetModuleStoreFile(string strTabName, string strKeyId)
        {
            string strSql = string.Format(@"select id,FILEDATA,FILETYPE,FILENAME from {0} where id=:keyid",
                strTabName);
            DbCommand cmd = dbGloab.GetSqlStringCommand(strSql);

            dbGloab.AddInParameter(cmd, "keyid", DbType.String, strKeyId);
            return dbGloab.ExecuteDataSet(cmd).Tables[0];
        }

        /// <summary>
        /// 在线申报文件上传信息
        /// </summary>
        /// <param name="fileUpload">文件上传信息</param>
        /// <param name="dType">数据存储类型</param>
        /// <param name="strModuleId">模块Id</param>
        /// <returns>返回执行结果</returns>
        public int StoreCailiaoUploadFile(FileUploadStruct fileUpload, DataStoreType dType, string strModuleId)
        {
            string strSql = string.Empty;

            if (dType == DataStoreType.Update)
            {
                strSql = string.Format(@"update {0} set {1}=:fileValue,
                {2}=:fileType where flag=1 and {3}=:keyid returning id into :newId", fileUpload.TableName,
                     fileUpload.FileStoreFieldName, fileUpload.FileTypeFieldName, fileUpload.KeyId);
            }
            if (dType == DataStoreType.Insert)
            {
                strSql = string.Format(@"insert into {0}({1},{2},{3},{4},flag,MODULE_ID) values(:fileValue,
                :fileType,:keyField,:resourceField,1,:moduleid) returning id into :newId", fileUpload.TableName,
                fileUpload.FileStoreFieldName, fileUpload.FileTypeFieldName, fileUpload.StoreKeyFieldName,
                fileUpload.StoreResourceFieldName, strModuleId);
            }
            DbCommand cmd = dbGloab.GetSqlStringCommand(strSql);

            dbGloab.AddInParameter(cmd, "fileValue", DbType.Binary, fileUpload.FileBytes);
            dbGloab.AddInParameter(cmd, "fileType", DbType.String, fileUpload.FileTypeFieldValue);

            if (dType == DataStoreType.Update)
            {
                dbGloab.AddInParameter(cmd, "keyid", DbType.String, fileUpload.KeyIdValue);
            }

            if (dType == DataStoreType.Insert)
            {
                dbGloab.AddInParameter(cmd, "keyField", DbType.String, fileUpload.StoreKeyValue);
                dbGloab.AddInParameter(cmd, "resourceField", DbType.String, fileUpload.StoreResourceNameValue);
                dbGloab.AddInParameter(cmd, "moduleid", DbType.String, strModuleId);
            }

            dbGloab.AddOutParameter(cmd, "newId", DbType.String, 32);

            int nReturn = dbGloab.ExecuteNonQuery(cmd);
            _newId = dbGloab.GetParameterValue(cmd, "newId").ToString();

            return nReturn;
        }

        /// <summary>
        /// 获取在线申报文件上传的资源信息
        /// </summary>
        /// <param name="strFileName">查找的文件名</param>
        /// <param name="strNewsId">地信中心外网申报信息id</param>
        /// <returns></returns>
        public DataTable GetCailiaoResourceInfo(string strFileName, string strNewsId)
        {
            string strSql = @"select id,FILEDATA,FILETYPE, AppDataId,FILENAME from XT_SERIAL_ATTACHMENT t where flag=1";

            if (!string.IsNullOrEmpty(strFileName))
            {
                strSql += string.Format(" and FILENAME like '%{0}%'", strFileName);
            }
            if (!string.IsNullOrEmpty(strNewsId))
            {
                strSql += string.Format(" and AppDataId='{0}'", strNewsId);
            }

            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取在线申报文件存储文件信息
        /// </summary>
        /// <param name="strTabName"></param>
        /// <param name="strKeyId"></param>
        /// <returns></returns>
        public DataTable GetCailiaoStoreFile(string strTabName, string strKeyId)
        {
            string strSql = string.Format(@"select AppDataId,FILEDATA,FILETYPE,FILENAME from {0} where id=:keyid and flag=1",
                strTabName);
            DbCommand cmd = dbGloab.GetSqlStringCommand(strSql);

            dbGloab.AddInParameter(cmd, "keyid", DbType.String, strKeyId);
            return dbGloab.ExecuteDataSet(cmd).Tables[0];
        }

        /// <summary>
        /// 获取在线申报上传的资源信息
        /// </summary>
        /// <param name="strType">文件类型</param>
        /// <param name="strNewsId">地信中心外网申报信息id</param>
        /// <returns></returns>
        /// 添加排序功能 edit by zhongjian 20091018
        public DataTable GetCailiaoInfo(string strType, string strNewsId)
        {
            string strSql = string.Format(@"select rownum 序号,t.* from (select a.id as moduleid,a.modulename,a.type,
                a.filedata as mudulefiledata,a.filename||'.'||a.filetype as mudulefilename,
                b.id as attachid,b.AppDataId,b.filedata as attachfiledata,
                b.filename||'.'|| b.filetype as attachfilename, b.flag,b.filename attfilename
                from (select * from XT_SERIAL_MODULE where type = '{0}') a,
                (select * from XT_SERIAL_ATTACHMENT where AppDataId = '{1}') b
                 where a.Id=b.Module_Id(+) ORDER BY a.ORDERDIELD) t", strType, strNewsId);

            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }        

        /// <summary>
        /// 获取在线申报上传的资源信息(地信中心接口专用)
        /// </summary>
        /// <param name="strType">文件类型</param>
        /// <param name="strNewsId">地信中心外网申报信息id</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20090925-->
        public DataTable GetAttinterfaceInfo(string strType, string strNewsId)
        {
            string strSql = string.Format(@"select rownum 序号,a.id as moduleid,a.modulename,a.type,
                a.filedata as mudulefiledata,a.filename||'.'||a.filetype as mudulefilename,
                b.id as attachid,b.AppDataId,b.filedata as attachfiledata,
                b.filename||'.'|| b.filetype as attachfilename, b.flag
                from (select * from XT_SERIAL_MODULE where type = '{0}') a,
                (select * from xt_serial_attinterface where AppDataId = '{1}') b
                 where a.Id=b.Module_Id(+)", strType, strNewsId);// order by a.modulename asc

            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取已上传的数量
        /// </summary>
        /// <param name="strIId">iid</param>
        /// <param name="strFlowname">flowname</param>
        /// <returns></returns>
        public string GetAlreadyUpdateCount(string strIId,string strFlowname)
        {
            Database dbTemp = BasicOperate.CreateNetOfficeDataBase();
            string strSql = string.Format(@"select count(*) from xt_serial_attachment where 
                AppDataId='{0}' and module_id in (select id from xt_serial_module
                where type='{1}')", strIId, strFlowname);
            DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
            object result = dbTemp.ExecuteScalar(dbCmd);
            if (result != null)
            {
                return result.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取需要数量
        /// </summary>
        /// <param name="strFlowname">flowname</param>
        /// <returns></returns>
        public string GetUpdateCount(string strFlowname)
        {
            Database dbTemp = BasicOperate.CreateNetOfficeDataBase();
            string strSql = string.Format(@"select count(*) from xt_serial_module where type='{0}' and flowid in (select c.id id from xt_workflow_define c where c.ispub = 1)", strFlowname);
            DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
            object result = dbTemp.ExecuteScalar(dbCmd);
            if (result != null)
            {
                return result.ToString();
            }
            else
            {
                return string.Empty;
            }
        }
   }
}
